<?php
class Voucher extends Admin_Controller
{
	
	function __construct()
	{
		parent::__construct();
		$this->load->model('fund_model','fund');
		$this->load->model('voucher_model','voucher');
		$this->load->model('voucher_balance_model','balance');
		$this->load->model('voucher_check_model','check');
		$this->load->model('voucher_receive_paid_model','receive_paid');
		$this->load->model('voucher_detail_model','voucher_detail');
	}
	
	function index($fund_id)
	{
		$user = login_data();
		$data['fund'] = $this->fund->get_row($fund_id);
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];		
		$data['agency_id'] = $agency_id;	
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);
		$where = '';
		$mode = (!empty($_GET['mode']))?$_GET['mode']:'';
		
		switch ($_GET['mode']) 
		{
			case 'edit':
				$where .= ' and mode = \'edit\'';
			    break;
			default:
			    $where .= ' and mode is null';
		}
		
		$where .= (!empty($_GET['voucher_type_id']))?' and vouchers.voucher_type_id = '.$_GET['voucher_type_id']:'';
		if(!empty($agency_id))$where .= ' and vouchers.agency_id = '.$agency_id;
		if(!empty($month))
		{
			$where .= ' and YEAR(vouchers.voucher_date) = '.$year.' and MONTH(vouchers.voucher_date) = '.$month;
		}
		else
		{
			list($start_date,$end_date) = year_budget($data['year']);
			$where .= ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		}
		$data['result'] = $this->voucher->where('vouchers.fund_id = '.$fund_id.$where)->sort('voucher_date')->order('desc')->get();
		$data['paginaion'] = $this->voucher->pagination();
		$this->template->build('voucher_index',$data);
	}
	
	function complete($fund_id)
	{
		$this->load->model('status_model','status');
		$this->load->model('setting/agency_detail_model','agency_detail');
		
		$user = login_data();
		$voucher_date =  $this->db->getOne('select max(voucher_date) from vouchers where is_balance = 1 and fund_id = '.$fund_id.' and agency_id = '.$user['agency_id']);
		$voucher_date = explode('-',$voucher_date);
		$data['status_year'] = $voucher_date[0];
		$data['status_month'] = $voucher_date[1];
		$data['user_id'] = $user['id'];
		$data['fund_id'] = $fund_id;
		$data['agency_id'] = $user['agency_id'];
		$data['status'] = '1';
		$data['id'] =  $this->db->getOne('select id from status where fund_id = '.$fund_id.' and agency_id = '.$user['agency_id'].' and status_year = '.$voucher_date[0].' and status_month ='.$voucher_date[1]);
		
		$this->status->save($data);
		
		unset($data);
		//**************Start lock agency ******************
		$data = $this->db->getRow('select * from agency_details where fund_id = ? and agency_id = ?',array($fund_id,$user['agency_id']));
		dbConvert($data);
		if($data)
		{
			$data['is_lock'] = '1';
		}
		else
		{
			$data['fund_id'] = $fund_id;
			$data['agency_id'] = $user['agency_id'];
			$data['is_lock'] = '1';
		}
		
		$this->agency_detail->save($data);
		//**************End lock agency ******************
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	
	
	function view($fund_id,$id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$data['result'] = $this->voucher->where('vouchers.fund_id = '.$fund_id)->get();
		$data['rs'] = $this->voucher->get_row($id);
		if($id)
		{
			$data['debits'] = $this->voucher_detail->where(' "TYPE" = \'dr\' and VOUCHER_ID = '.$id)->get();
			$data['credits'] = $this->voucher_detail->where(' "TYPE" = \'cr\' and VOUCHER_ID = '.$id)->get();
		}
		$this->template->append_metadata(js_validate());
		$this->template->append_metadata(js_datepicker());
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_view',$data);
	}
	
	function form($fund_id,$id = NULL)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$data['result'] = $this->voucher->where('vouchers.fund_id = '.$fund_id.' and vouchers.agency_id = '.login_data('agency_id').' and is_balance = 1')->get();
		$data['rs'] = $this->voucher->get_row($id);
		if($id)
		{
			$data['debits'] = $this->voucher_detail->where(' "TYPE" = \'dr\' and VOUCHER_ID = '.$id)->get();
			$data['credits'] = $this->voucher_detail->where(' "TYPE" = \'cr\' and VOUCHER_ID = '.$id)->get();
		}
		
		$data['approve'] = $this->db->getRow('select * from approves where fund_id = '.$fund_id.' and agency_id = '.login_data('agency_id'));
		dbConvert($data['approve']);

		$this->template->append_metadata(js_validate());
		$this->template->append_metadata(js_datepicker());

		if((@$_GET['mode']=='edit')||($data['rs']['mode']=='edit'))
		{
			$this->template->build('edit_form',$data);
		}
		elseif(($data['result'])&&($data['rs']['is_balance']<>'1'))
		{
			$this->template->build('voucher_form',$data);
		}
		else
		{
			$this->template->build('balance_form',$data);
		}
	}
	
	function cash_flow($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		
		list($start_date,$end_date) = year_budget($year-1);
		$where2 = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$where2 .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		$data['result'] = $this->voucher_detail->get('
		select * from 
		(
		
		select accounts.id,accounts.name,accounts.category_id,price,price2 from accounts
		left join 
		(select account_id,sum(price) price from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' '.$where.' and voucher_id in (select voucher_id from voucher_details where account_id = 1) and vouchers.is_balance is null and vouchers.mode is null group by account_id) b
		on b.account_id = accounts.id
		left join 
		(select account_id,sum(price) price2 from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' '.$where2.' and voucher_id in (select voucher_id from voucher_details where account_id = 1) and vouchers.is_balance is null and vouchers.mode is null group by account_id) c
		on c.account_id = accounts.id
		where accounts.category_id = 4
		and accounts.fund_id = '.$fund_id.'
		
		union
		
		select accounts.id,accounts.name,accounts.category_id,price,price2 from accounts
		left join 
		(select account_id,sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where.' and voucher_id in (select voucher_id from voucher_details where account_id = 1) and vouchers.is_balance is null and vouchers.mode is null group by account_id) b
		on b.account_id = accounts.id
		left join 
		(select account_id,sum(price) price2 from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where2.' and voucher_id in (select voucher_id from voucher_details where account_id = 1) and vouchers.is_balance is null and vouchers.mode is null group by account_id) c
		on c.account_id = accounts.id
		where accounts.category_id = 5
		and accounts.fund_id = '.$fund_id.'
		) a
		order by category_id
		',FALSE);
		$this->db->debug = FALSE;
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_cash_flow',$data);
	}
	
	function save()
	{
		if($_POST)
		{
			if(empty($_POST['id']))
			{
				add_log('เพิ่มใบสำคัญการลงบัญชี กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" วันเดือนปีที่ลงบัญชี '.$_POST['voucher_date']);
			}
			else
			{
				add_log('แก้ไขใบสำคัญการลงบัญชี กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" วันเดือนปีที่ลงบัญชี '.$_POST['voucher_date']);
			}
			$_POST['voucher_date'] = date_to_mysql($_POST['voucher_date'],TRUE);
			$_POST['user_id'] = $this->session->userdata('id');
			$_POST['agency_id'] = login_data('agency_id');
			
			$approve = $this->db->getRow('select * from approves where fund_id = '.$_POST['fund_id'].' and agency_id = '.$_POST['agency_id']);
			dbConvert($approve);
			$_POST['examiner'] = $approve['examiner'];
			$_POST['examiner_position'] = $approve['examiner_position'];
			$_POST['endorsee'] = $approve['endorsee'];
			$_POST['endorsee_position'] = $approve['endorsee_position'];
			
			$voucher_id = $this->voucher->save($_POST);
			$this->voucher_detail->delete('voucher_id',$voucher_id);
			foreach($_POST['account_id'] as $type => $item)
			{
				foreach($item as $key => $value)
				{
					$data['account_id'] = $value;
					$data['type'] = $type;
					$data['price'] = str_replace(",", "", $_POST['price'][$type][$key]);
					$data['voucher_id'] = $voucher_id;
					if(($data['price']<>"")&&($data['price']<>'0.00'))
					{
						$this->voucher_detail->save($data);
					}
					unset($data);
				}
			}
			
			set_notify('success', lang('save_data_complete'));
		}
		if(!empty($_POST['mode']))
		{
			redirect('voucher/index/'.$_POST['fund_id'].'?mode='.$_POST['mode']);
		}
		else
		{
			redirect('voucher/index/'.$_POST['fund_id']);
		}
	}
	
	function paper_edit($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		$data['agency_id'] = $agency_id;
		//**************Start where section ******************
		$where = '';
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$balance_where = ' and YEAR(vouchers.voucher_date) = '.($year-1).' and MONTH(vouchers.voucher_date) = 10';
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		
		//**************End where section ******************
		$data['result'] = $this->voucher_detail->get('
		select accounts.name,accounts.code,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then 0 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else 0 end) cr_sum,
		(CASE when (COALESCE(c1.dr_balance,0) - COALESCE(d1.cr_balance,0) + COALESCE(a1.dr_month,0) - COALESCE(b1.cr_month,0)) < 0 then 0 else (COALESCE(c1.dr_balance,0) - COALESCE(d1.cr_balance,0) + COALESCE(a1.dr_month,0) - COALESCE(b1.cr_month,0)) end) dr_sum1,
		(CASE when (COALESCE(c1.dr_balance,0) - COALESCE(d1.cr_balance,0) + COALESCE(a1.dr_month,0) - COALESCE(b1.cr_month,0)) < 0 then (COALESCE(c1.dr_balance,0) - COALESCE(d1.cr_balance,0) + COALESCE(a1.dr_month,0) - COALESCE(b1.cr_month,0)) * -1 else 0 end) cr_sum1,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum2,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum2,
		a3.dr_month dr_edit,b3.cr_month cr_edit,
		(CASE when (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) < 0 then 0 else (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) end) dr_sum4,
		(CASE when (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) < 0 then (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) * -1 else 0 end) cr_sum4
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' and vouchers.agency_id = 2 group by account_id) a1
		on a1.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' and vouchers.agency_id = 2 group by account_id) b1
		on b1.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' and vouchers.agency_id = 2 group by account_id) c1
		on c1.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' and vouchers.agency_id = 2 group by account_id) d1
		on d1.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' and vouchers.agency_id > 2 group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' and vouchers.agency_id > 2 group by account_id) b2
		on b2.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' and vouchers.agency_id > 2 group by account_id) c2
		on c2.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' and vouchers.agency_id > 2 group by account_id) d2
		on d2.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode = \'edit\' ) '.$where.' group by account_id) a3
		on a3.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode = \'edit\' ) '.$where.' group by account_id) b3
		on b3.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a4
		on a4.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b4
		on b4.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c4
		on c4.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d4
		on d4.account_id = accounts.id
		where fund_id = '.$fund_id.'
		',FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_paper_edit',$data);
	}
	
	function paper_close($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		$data['agency_id'] = $agency_id;
		if($fund_id<>'1')$agency_id='';
		
		//**************Start where section ******************
		$where = '';
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$balance_where = ' and YEAR(vouchers.voucher_date) = '.($year-1).' and MONTH(vouchers.voucher_date) = 10';
		
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
		}
		//**************End where section ******************
		
		$sql = '
		select accounts.name,accounts.code,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum,
		a3.dr_month dr_edit,b3.cr_month cr_edit,
		(CASE when (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) < 0 then 0 else (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) end) dr_sum4,
		(CASE when (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) < 0 then (COALESCE(c4.dr_balance,0) - COALESCE(d4.cr_balance,0) + COALESCE(a4.dr_month,0) - COALESCE(b4.cr_month,0)) * -1 else 0 end) cr_sum4,
		(CASE when (COALESCE(c5.dr_balance,0) - COALESCE(d5.cr_balance,0) + COALESCE(a5.dr_month,0) - COALESCE(b5.cr_month,0)) < 0 then 0 else (COALESCE(c5.dr_balance,0) - COALESCE(d5.cr_balance,0) + COALESCE(a5.dr_month,0) - COALESCE(b5.cr_month,0)) end) dr_sum5,
		(CASE when (COALESCE(c5.dr_balance,0) - COALESCE(d5.cr_balance,0) + COALESCE(a5.dr_month,0) - COALESCE(b5.cr_month,0)) < 0 then (COALESCE(c5.dr_balance,0) - COALESCE(d5.cr_balance,0) + COALESCE(a5.dr_month,0) - COALESCE(b5.cr_month,0)) * -1 else 0 end) cr_sum5
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode in (\'closed\',\'depreciation\')) '.$where.'  group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode in (\'closed\',\'depreciation\')) '.$where.'  group by account_id) b2
		on b2.account_id = accounts.id
		
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode = \'edit\' ) '.$where.' group by account_id) a3
		on a3.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode = \'edit\' ) '.$where.' group by account_id) b3
		on b3.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a4
		on a4.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b4
		on b4.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c4
		on c4.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d4
		on d4.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode in (\'edit\',\'closed\',\'depreciation\')) ) '.$where.' group by account_id) a5
		on a5.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode in (\'edit\',\'closed\',\'depreciation\')) ) '.$where.' group by account_id) b5
		on b5.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c5
		on c5.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d5
		on d5.account_id = accounts.id
		where fund_id = '.$fund_id.'
		';
		$data['result'] = $this->voucher_detail->get($sql,FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_paper_close',$data);
	}
	
	function work_display($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		
		list($start_date,$end_date) = year_budget($year-1);
		$where2 = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\' and vouchers.fund_id ='.$fund_id;
		
		
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$where2 .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		$data['result'] = $this->voucher_detail->get('
		select * from 
		(
		
		select accounts.id,accounts.name,accounts.category_id,price,price2 from accounts
		left join 
		(select account_id,sum(price) price from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) b
		on b.account_id = accounts.id
		left join 
		(select account_id,sum(price) price2 from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' '.$where2.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) c
		on c.account_id = accounts.id
		where accounts.category_id = 4
		and accounts.group_id is null
		and accounts.fund_id = '.$fund_id.'
		
		union
		
		select accounts.id,accounts.name,accounts.category_id,price,price2 from accounts
		left join 
		(select account_id,sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) b
		on b.account_id = accounts.id
		left join 
		(select account_id,sum(price) price2 from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where2.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) c
		on c.account_id = accounts.id
		where accounts.category_id = 5
		and accounts.group_id is null
		and accounts.fund_id = '.$fund_id.'
		
		union
		
		select account_groups.id,account_groups.name,5 category_id,price,price2 from account_groups
		left join 
		(select group_id,sum(price) price from voucher_details left join accounts on accounts.id = voucher_details.account_id left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) and accounts.category_id = 5
		and accounts.group_id in (2,3,4,5)
		and accounts.fund_id = '.$fund_id.' group by group_id) b
		on b.group_id = account_groups.id
		left join 
		(select group_id,sum(price) price2 from voucher_details left join accounts on accounts.id = voucher_details.account_id left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where2.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) and accounts.category_id = 5
		and accounts.group_id in (2,3,4,5)
		and accounts.fund_id = '.$fund_id.' group by group_id) c
		on c.group_id = account_groups.id
		where account_groups.id in (2,3,4,5)
		
		) a
		order by category_id
		',FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_work_display',$data);
	}
	
	function money_display($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		//**************Start where section ******************
		$where = '';
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\' and vouchers.fund_id = '.$fund_id;
		$balance_where = ' and YEAR(vouchers.voucher_date) = '.($year-1).' and MONTH(vouchers.voucher_date) = 10  and vouchers.fund_id = '.$fund_id;
		
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year-1);
		$where2 = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\' and vouchers.fund_id = '.$fund_id;
		$balance_where2 = ' and YEAR(vouchers.voucher_date) = '.($year-2).' and MONTH(vouchers.voucher_date) = 10  and vouchers.fund_id = '.$fund_id;
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
			
			$where2 .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where2 .= ' and vouchers.agency_id = '.$agency_id;
		}
		if($fund_id==1)
		{
			$karupan = 6;
			$depreciate = 7;
		}
		elseif($fund_id==2)
		{
			$karupan = 60;
			$depreciate = 98;
		}
		
		$karupan = $this->db->getOne('select id from accounts where group_id = 6 and fund_id = '.$fund_id);
		$depreciate  = $this->db->getOne('select id from accounts where group_id = 7 and fund_id = '.$fund_id);
		//**************End where section ******************
		$data['result'] = $this->voucher_detail->get('
		select * from (
		select accounts.id,accounts.name,accounts.type_id,accounts.category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) price2
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) b2
		on b2.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) c2
		on c2.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) d2
		on d2.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.group_id is null
		and accounts.category_id in (1,2,3)
		union
		select account_groups.id,account_groups.name,1 type_id,1 category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) price2
		from account_groups
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by group_id) a
		on a.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by group_id) b
		on b.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by group_id) c
		on c.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by group_id) d
		on d.group_id = account_groups.id
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by group_id) a2
		on a2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by group_id) b2
		on b2.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by group_id) c2
		on c2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by group_id) d2
		on d2.group_id = account_groups.id
		where account_groups.id = 1
		union
		select account_groups.id,account_groups.name,2 type_id,1 category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) - (CASE when (COALESCE(g.dr_balance,0) - COALESCE(h.cr_balance,0) + COALESCE(e.dr_month,0) - COALESCE(f.cr_month,0)) < 0 then (COALESCE(g.dr_balance,0) - COALESCE(h.cr_balance,0) + COALESCE(e.dr_month,0) - COALESCE(f.cr_month,0)) * -1 else (COALESCE(g.dr_balance,0) - COALESCE(h.cr_balance,0) + COALESCE(e.dr_month,0) - COALESCE(f.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) - (CASE when (COALESCE(g2.dr_balance,0) - COALESCE(h2.cr_balance,0) + COALESCE(e2.dr_month,0) - COALESCE(f2.cr_month,0)) < 0 then (COALESCE(g2.dr_balance,0) - COALESCE(h2.cr_balance,0) + COALESCE(e2.dr_month,0) - COALESCE(f2.cr_month,0)) * -1 else (COALESCE(g2.dr_balance,0) - COALESCE(h2.cr_balance,0) + COALESCE(e2.dr_month,0) - COALESCE(f2.cr_month,0)) end) price2
		from account_groups
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' and voucher_details.account_id = '.$karupan.' group by group_id) a
		on a.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' and voucher_details.account_id = '.$karupan.' group by group_id) b
		on b.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' and voucher_details.account_id = '.$karupan.' group by group_id) c
		on c.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' and voucher_details.account_id = '.$karupan.' group by group_id) d
		on d.group_id = account_groups.id
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' and voucher_details.account_id = '.$karupan.' group by group_id) a2
		on a2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' and voucher_details.account_id = '.$karupan.' group by group_id) b2
		on b2.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' and voucher_details.account_id = '.$karupan.' group by group_id) c2
		on c2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' and voucher_details.account_id = '.$karupan.' group by group_id) d2
		on d2.group_id = account_groups.id
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' and voucher_details.account_id = '.$depreciate.' group by group_id) e
		on e.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' and voucher_details.account_id = '.$depreciate.' group by group_id) f
		on f.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' and voucher_details.account_id = '.$depreciate.' group by group_id) g
		on g.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' and voucher_details.account_id = '.$depreciate.' group by group_id) h
		on h.group_id = account_groups.id
		left join (select group_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' and voucher_details.account_id = '.$depreciate.' group by group_id) e2
		on e2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' and voucher_details.account_id = '.$depreciate.' group by group_id) f2
		on f2.group_id = account_groups.id
		left join (select group_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' and voucher_details.account_id = '.$depreciate.' group by group_id) g2
		on g2.group_id = account_groups.id
		left join (select group_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id left join accounts on accounts.id = voucher_details.account_id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' and voucher_details.account_id = '.$depreciate.' group by group_id) h2
		on h2.group_id = account_groups.id
		where account_groups.id = 6
		) a
		order by category_id,type_id,id
		',FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_money_display',$data);
	}
	
	function ps($fund_id)
	{
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		//**************Start where section ******************
		$where = '';
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$balance_where = ' and YEAR(vouchers.voucher_date) = '.($year-1).' and MONTH(vouchers.voucher_date) = 10';
		
		$year = (!empty($_GET['year']))?$_GET['year']-543:date('Y');
		list($start_date,$end_date) = year_budget($year-1);
		$where2 = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\' and vouchers.fund_id = '.$fund_id;
		$balance_where2 = ' and YEAR(vouchers.voucher_date) = '.($year-2).' and MONTH(vouchers.voucher_date) = 10  and vouchers.fund_id = '.$fund_id;
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
			
			$where2 .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where2 .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		//**************End where section ******************
		$data['money'] = $this->voucher_detail->get('
		select * from (
		select accounts.id,accounts.name,accounts.type_id,accounts.category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) price2
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) b2
		on b2.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) c2
		on c2.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) d2
		on d2.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.group_id  = 1
		and accounts.category_id = 1
		) a
		order by category_id,type_id,id
		',FALSE);
		
		$data['karupan'] = $this->voucher_detail->get('
		select * from (
		select accounts.id,accounts.name,accounts.type_id,accounts.category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) price2
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) b2
		on b2.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) c2
		on c2.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) d2
		on d2.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.group_id  = 6
		and accounts.category_id = 1
		) a
		order by category_id,type_id,id
		',FALSE);
		
		$data['karupan_delete'] = $this->voucher_detail->get('
		select * from (
		select accounts.id,accounts.name,accounts.type_id,accounts.category_id,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) * -1 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a.dr_month,0) - COALESCE(b.cr_month,0)) end) price,
		(CASE when (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else (COALESCE(c2.dr_balance,0) - COALESCE(d2.cr_balance,0) + COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) price2
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) a2
		on a2.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null) '.$where2.' group by account_id) b2
		on b2.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) c2
		on c2.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null) '.$balance_where2.' group by account_id) d2
		on d2.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.group_id  = 7
		and accounts.category_id = 1
		) a
		order by category_id,type_id,id
		',FALSE);
		
		$data['work'] = $this->voucher_detail->get('
		select accounts.id,accounts.name,accounts.category_id,price,price2 from accounts
		left join 
		(select account_id,sum(price) price from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) b
		on b.account_id = accounts.id
		left join 
		(select account_id,sum(price) price2 from voucher_details left join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' '.$where2.' and voucher_id in (select id from vouchers where is_balance is null and mode is null) group by account_id) c
		on c.account_id = accounts.id
		where accounts.category_id = 5
		and accounts.group_id = 3
		and accounts.fund_id = '.$fund_id.'
		',FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_ps',$data);
	}
	
	
	function delete($id)
	{
		$rs = $this->voucher->get_row($id);
		add_log('เพิ่มใบสำคัญการลงบัญชี กองทุน  "'.$this->fund->get_one('name',$rs['fund_id']).'" วันเดือนปีที่ลงบัญชี '.$rs['voucher_date']);
		$this->voucher_detail->delete('voucher_id',$id);
		$this->voucher->delete($id);
		set_notify('success', lang('delete_data_complete'));
		redirect($_SERVER['HTTP_REFERER']);
	}
	
	function detail_delete($id)
	{
		$this->voucher_detail->delete($id);
	}
	
}
?>